﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using System.Linq;
using LIB.Core;

public class ExcelHelper
{
    private const int rowLimit = 0xfde8;

    private static string getCell(Type type, object cellData)
    {
        object obj2 = (cellData is DBNull) ? "" : cellData;
        if ((type.Name.Contains("Int") || type.Name.Contains("Double")) || type.Name.Contains("Decimal"))
        {
            return string.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>", obj2);
        }
        if (type.Name.Contains("Date") && (obj2.ToString() != string.Empty))
        {
            return string.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}</Data></Cell>", Convert.ToDateTime(obj2).ToString("yyyy-MM-dd"));
        }
        return string.Format("<Cell><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(obj2.ToString()));
    }

    public static string GetExcelXml(DataSet dsInput, string filename)
    {
        string format = getWorkbookTemplate();
        string str2 = getWorksheets(dsInput);
        return string.Format(format, str2);
    }
    public static string GetExcelXml(DataTable dtInput, string filename, string b_truong)
    {
        string format = getWorkbookTemplate();
        DataSet source = new DataSet();
        source.Tables.Add(dtInput.Copy());
        string str2 = getWorksheets(dtInput, b_truong);
        return string.Format(format, str2);
    }
    public static string GetExcelXml(DataTable dtInput, string filename)
    {
        string format = getWorkbookTemplate();
        DataSet source = new DataSet();
        source.Tables.Add(dtInput.Copy());
        string str2 = getWorksheets(source);
        return string.Format(format, str2);
    }

    private static string getWorkbookTemplate()
    {
        StringBuilder builder = new StringBuilder(0x332);
        builder.AppendFormat("<?xml version=\"1.0\"?>{0}", Environment.NewLine);
        builder.AppendFormat("<?mso-application progid=\"Excel.Sheet\"?>{0}", Environment.NewLine);
        builder.AppendFormat("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"{0}", Environment.NewLine);
        builder.AppendFormat(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"{0}", Environment.NewLine);
        builder.AppendFormat(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"{0}", Environment.NewLine);
        builder.AppendFormat(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"{0}", Environment.NewLine);
        builder.AppendFormat(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">{0}", Environment.NewLine);
        builder.AppendFormat(" <Styles>{0}", Environment.NewLine);
        builder.AppendFormat("  <Style ss:ID=\"Default\" ss:Name=\"Normal\">{0}", Environment.NewLine);
        builder.AppendFormat("   <Alignment ss:Vertical=\"Bottom\"/>{0}", Environment.NewLine);
        builder.AppendFormat("   <Borders/>{0}", Environment.NewLine);
        builder.AppendFormat("   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>{0}", Environment.NewLine);
        builder.AppendFormat("   <Interior/>{0}", Environment.NewLine);
        builder.AppendFormat("   <NumberFormat/>{0}", Environment.NewLine);
        builder.AppendFormat("   <Protection/>{0}", Environment.NewLine);
        builder.AppendFormat("  </Style>{0}", Environment.NewLine);
        builder.AppendFormat("  <Style ss:ID=\"s62\">{0}", Environment.NewLine);
        builder.AppendFormat("   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>{0}", Environment.NewLine);
        builder.AppendFormat("   <Borders/>{0}", Environment.NewLine);
        builder.AppendFormat("  </Style>{0}", Environment.NewLine);

        builder.AppendFormat("  <Style ss:ID=\"m284322144\">{0}", Environment.NewLine);
        builder.AppendFormat("      <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("   <Font ss:FontName=\"Calibri\" x:Family=\"Swiss\" ss:Size=\"11\" ss:Color=\"#000000\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Borders>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      </Borders>{0}", Environment.NewLine);
        builder.AppendFormat("  </Style>{0}", Environment.NewLine);

        builder.AppendFormat("  <Style ss:ID=\"s63\">{0}", Environment.NewLine);
        builder.AppendFormat("   <NumberFormat ss:Format=\"Short Date\"/>{0}", Environment.NewLine);
        //quannm: Thêm border
        builder.AppendFormat("      <Borders>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>{0}", Environment.NewLine);
        builder.AppendFormat("      </Borders>{0}", Environment.NewLine);
        builder.AppendFormat("  </Style>{0}", Environment.NewLine);
        builder.AppendFormat(" </Styles>{0}", Environment.NewLine);
        builder.Append(@"{0}\r\n</Workbook>");
        return builder.ToString();
    }

    private static string getWorksheets(DataSet source)
    {
        StringWriter writer = new StringWriter();
        if ((source == null) || (source.Tables.Count == 0))
        {
            writer.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            return writer.ToString();
        }
        foreach (DataTable table in source.Tables)
        {
            if (table.Rows.Count == 0)
            {
                writer.Write("<Worksheet ss:Name=\"" + replaceXmlChar(table.TableName) + "\">\r\n<Table>\r\n<Row><Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            }
            else
            {
                int num = 0;
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    if ((i % 0xfde8) == 0)
                    {
                        if ((i / 0xfde8) > num)
                        {
                            writer.Write("\r\n</Table>\r\n</Worksheet>");
                            num = i / 0xfde8;
                        }
                        writer.Write("\r\n<Worksheet ss:Name=\"" + replaceXmlChar(table.TableName) + (((i / 0xfde8) == 0) ? "" : Convert.ToString((int)(i / 0xfde8))) + "\">\r\n<Table>");
                        writer.Write("\r\n<Row>");
                        foreach (DataColumn column in table.Columns)
                        {
                            writer.Write(string.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", replaceXmlChar(column.ColumnName)));
                        }
                        writer.Write("</Row>");
                    }
                    writer.Write("\r\n<Row>");
                    foreach (DataColumn column2 in table.Columns)
                    {
                        writer.Write(getCell(column2.DataType, table.Rows[i][column2.ColumnName]));
                    }
                    writer.Write("</Row>");
                }
                writer.Write("\r\n</Table>\r\n</Worksheet>");
            }
        }
        return writer.ToString();
    }
    /// <summary>
    ///Quannm: Đưa file excel theo group
    /// </summary>
    /// <param name="b_dt"></param>
    /// <param name="b_truong"></param>
    /// <returns></returns>
    private static string getWorksheets(DataTable b_dt, string b_truong)
    {
        var b_qr = from p in b_dt.AsEnumerable()
                   group p by new
                   {
                       ma_dt = p.Field<string>("ma_dt"),
                       ten_dt = p.Field<string>("ten_dt")
                   };
        StringWriter writer = new StringWriter();
        if ((b_dt == null) || (b_dt.Rows.Count == 0))
        {
            writer.Write("<Worksheet ss:Name=\"Sheet1\">\r\n<Table>\r\n<Row><Cell><Data ss:Type=\"String\"></Data></Cell></Row>\r\n</Table>\r\n</Worksheet>");
            return writer.ToString();
        }
        int num = 0;
        string b_gtri = string.Empty;
        bool b_okie = true;

        b_gtri = LibConvert.OBJ_STR(b_dt.Rows[0]["ten_dvi"]);
        for (int i = 0; i < b_dt.Rows.Count; i++)
        {
            if ((i % 65000) == 0)
            {
                if ((i / 65000) > num)
                {
                    writer.Write("\r\n</Table>\r\n</Worksheet>");
                    num = i / 65000;
                }
                writer.Write("\r\n<Worksheet ss:Name=\"" + replaceXmlChar(b_dt.TableName) + (((i / 65000) == 0) ? "" : Convert.ToString((int)(i / 65000))) + "\">\r\n<Table>");

                foreach (var items in b_qr)
                {
                    writer.Write("<Column ss:StyleID=\"s62\" ss:AutoFitWidth=\"0\" ss:Width=\"100\" />");
                }
                writer.Write("\r\n<Row>");
                //Lặp tiêu đề khi vượt quá 65000 dòng
                writer.Write("<Cell ss:StyleID=\"m284322144\" ><Data ss:Type=\"String\">Tên đơn vị</Data></Cell>");
                foreach (var items in b_qr)
                {
                    writer.Write(string.Format("<Cell ss:StyleID=\"m284322144\" ><Data ss:Type=\"String\">{0}</Data></Cell>", MergeValue(items.Key.ten_dt, items.Key.ma_dt)));
                }
                writer.Write("</Row>");
            }
        //Ghi data
        tiep:
            if (b_okie)
            {
                writer.Write("\r\n<Row>");
                writer.Write(getCell(b_dt.Rows[i]["ten_dvi"].GetType(), b_dt.Rows[i]["ten_dvi"]));
            }
            if (b_gtri == LibConvert.OBJ_STR(b_dt.Rows[i]["ten_dvi"]))
            {
                if (b_qr.Select(o => o.Key.ma_dt == LibConvert.OBJ_STR(b_dt.Rows[i]["ma_dt"])).Count() > 0)
                {
                    b_okie = false;
                    writer.Write(getCell(b_dt.Rows[i][b_truong].GetType(), b_dt.Rows[i][b_truong]));
                }
            }
            else
            {
                b_gtri = LibConvert.OBJ_STR(b_dt.Rows[i]["ten_dvi"]);
                writer.Write("</Row>");
                b_okie = true;
                goto tiep;
            }
        }
        writer.Write("</Row>");
        writer.Write("\r\n</Table>\r\n</Worksheet>");
        return writer.ToString();
    }
    static string MergeValue(string value, string values1)
    {
        return value + " (" + values1 + ")";
    }
    private static string replaceXmlChar(string input)
    {
        input = input.Replace("&", "&amp");
        input = input.Replace("<", "&lt;");
        input = input.Replace(">", "&gt;");
        input = input.Replace("\"", "&quot;");
        input = input.Replace("'", "&apos;");
        return input;
    }
    public static void ExportToExcel(DataTable dsInput, string filename, HttpResponse response, string b_loai)
    {
        string excelXml = GetExcelXml(dsInput, filename, b_loai);
        response.Clear();
        response.AppendHeader("Content-Type", "application/vnd.ms-excel");
        response.AppendHeader("Content-disposition", "attachment;filename=" + filename);
        response.Write(excelXml);
        response.Flush();
        response.End();
    }
    public static void ToExcel(DataSet dsInput, string filename, HttpResponse response, bool ok)
    {
        string excelXml = GetExcelXml(dsInput, filename);
        response.Clear();
        response.AppendHeader("Content-Type", "application/vnd.ms-excel");
        response.AppendHeader("Content-disposition", "attachment;filename=" + filename);
        response.Write(excelXml);
        response.Flush();
        response.End();
    }

    public static void ToExcel(DataSet dsInput, string filename, HttpResponse response)
    {
        DataTable table = dsInput.Tables[0];

        var workbook = new HSSFWorkbook();
        NpoiHelper npoiHelper = new NpoiHelper(workbook);

        ICell cell = null;
        IRow row = null;
        ISheet sheet = null;
        int rowIndex = 0;
        double k = 0;
        int column_count = table.Columns.Count;
        foreach (DataRow dataRow in table.Rows)
        {

            //Quannm: Neus co nhieu dong tư tach lam nhieu sheets
            if (k == 0 || k % 10000 == 0)
            {
                sheet = workbook.CreateSheet("Sheet1" + (Math.Round(k / 10000)).ToString());
                //Table Header
                //Line 1
                row = NpoiHelper.GetRow(sheet, rowIndex);
                for (int i = 0; i < column_count; i++)
                {
                    npoiHelper.GetTableHeaderCenter(row, i).SetCellValue(table.Columns[i].ColumnName);
                }
                rowIndex = 0;
            }
            k++;
            row = NpoiHelper.GetRow(sheet, ++rowIndex);

            for (int i = 0; i < column_count; i++)
            {
                if (table.Columns[i].DataType.Name == "Decimal")
                {
                    if (dataRow[i].ToString() == "")
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(0.0);
                    }
                    else
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(Double.Parse(dataRow[i].ToString()));
                    }
                }
                else
                    npoiHelper.GetTableCell(row, i).SetCellValue(dataRow[i].ToString());
            }

        }

        ExcelHelper.ToExcel(workbook, filename, response);
    }

    public static void ExportToExcel(string hk, DataSet b_ds, string filename, HttpResponse response)
    {

        DataTable b_dt = new DataTable();
        b_dt = b_ds.Tables[0];
        DataTable b_dt_mon = b_ds.Tables[1];
        var workbook = new HSSFWorkbook();
        NpoiHelper npoiHelper = new NpoiHelper(workbook);

        ICell cell = null;
        IRow row = null;
        ISheet sheet = null;
        int rowIndex = 0;
        int column_count = b_dt.Columns.Count;

        sheet = workbook.CreateSheet("Sheet1");

        //Line 1
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetTableCell(row, 0).SetCellValue("TRƯỜNG CAO ĐẲNG MÚA VIỆT NAM");
        //Line 2
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetTableCell(row, 0).SetCellValue("Phòng đào tạo và công tác học sinh sinh viên");

        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetTableCell(row, 3).SetCellValue("BẢNG ĐIỂM HỌC KỲ " + hk);
        rowIndex = rowIndex + 3;
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        string b_ten;
        for (int i = 0; i < column_count; i++)
        {
            DataRow[] _row = b_dt_mon.Select("Id_mon='" + b_dt.Columns[i].ColumnName + "'");
            if (_row.Length == 0)
                b_ten = b_dt.Columns[i].ColumnName;
            else
                b_ten = _row[0]["TenMon"].ToString();
            npoiHelper.GetTableHeaderCenter(row, i).SetCellValue(b_ten);
        }

        foreach (DataRow dataRow in b_dt.Rows)
        {
            row = NpoiHelper.GetRow(sheet, ++rowIndex);

            for (int i = 0; i < column_count; i++)
            {
                if (b_dt.Columns[i].DataType.Name == "Decimal")
                {
                    if (dataRow[i].ToString() == "")
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(0.0);
                    }
                    else
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(Double.Parse(dataRow[i].ToString()));
                    }
                }
                else
                    npoiHelper.GetTableCell(row, i).SetCellValue(dataRow[i].ToString());
            }
        }
        ExcelHelper.ToExcel(workbook, filename, response);
    }
    //Xuất báo cáo cuối năm
    public static void ExportBC_CN(DataSet b_ds, string filename, HttpResponse response, string ten)
    {

        DataTable b_dt = new DataTable();
        b_dt = b_ds.Tables[0];
        DataTable b_dt_mon = b_ds.Tables[1];
        var workbook = new HSSFWorkbook();
        NpoiHelper npoiHelper = new NpoiHelper(workbook);

        ICell cell = null;
        IRow row = null;
        ISheet sheet = null;
        int rowIndex = 0;
        int column_count = b_dt.Columns.Count;

        sheet = workbook.CreateSheet("Sheet1");


        //Line 1
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetReportHeader(row, 0).SetCellValue("TRƯỜNG CAO ĐẲNG MÚA VIỆT NAM");
        //Line 2
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetReportHeader(row, 0).SetCellValue("Phòng đào tạo và công tác học sinh sinh viên");

        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetReportHeader(row, 3).SetCellValue("BẢNG ĐIỂM THI TỐT NGHIỆP VÀ PHÂN LOẠI TOÀN KHÓA");

        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        npoiHelper.GetReportHeader(row, 3).SetCellValue("LỚP " + ten.ToUpper());

        rowIndex = rowIndex + 3;
        row = NpoiHelper.GetRow(sheet, ++rowIndex);
        string b_ten = "";

        sheet.SetColumnWidth(1, 8000);
        sheet.SetColumnWidth(0, 3000);
        npoiHelper.GetTableHeaderCenter(sheet, row, 0, rowIndex, rowIndex + 3, 0, 0).SetCellValue("Mã sinh viên");
        npoiHelper.GetTableHeaderCenter(sheet, row, 1, rowIndex, rowIndex + 3, 1, 1).SetCellValue("Họ và tên");
        int dem = 0, j = 0;
        for (int i = 2; i < column_count; i++)
        {
            string index = b_dt.Columns[i].ColumnName.Substring(1, 1);
            if (b_dt.Columns[i].ColumnName.IndexOf("_") >= 0)
            {
                dem = i;
                string nam = b_dt.Columns[i].ColumnName.Substring(3, 1);
                DataRow[] _row = b_dt_mon.Select("ma='" + nam + "'");

                if (_row.Length == 0)
                {
                    b_ten = "";
                }
                else
                {
                    if (index == "1")
                        b_ten = "HK I ";
                    else if (index == "2") b_ten = "HK II";
                }
                if (i % 2 == 0)
                {
                    row = NpoiHelper.GetRow(sheet, rowIndex + 1);
                    string ten1 = "Năm học " + _row[0]["ten"].ToString();
                    npoiHelper.GetTableHeaderWarp(sheet, row, i, rowIndex + 1, rowIndex + 2, i, i + 1).SetCellValue(ten1);
                }
                sheet.SetColumnWidth(i, 3000);
                row = NpoiHelper.GetRow(sheet, rowIndex + 3);
                npoiHelper.GetTableHeaderCenter(sheet, row, i, rowIndex + 3, rowIndex + 3, i, i).SetCellValue(b_ten);
            }
            else
            {
                if (dem > 0)
                {

                    row = NpoiHelper.GetRow(sheet, rowIndex);
                    row.Height = 300;
                    npoiHelper.GetTableHeaderCenter(sheet, row, 2, rowIndex, rowIndex, 2, dem).SetCellValue("PHÂN LOẠI TOÀN KHÓA");
                    npoiHelper.GetTableHeaderWarp(sheet, row, dem + 1, rowIndex, rowIndex + 2, dem + 1, b_dt.Columns.Count - 1).SetCellValue("Điểm Thi Tốt Nghiệp");
                    dem = 0;
                }
                row = NpoiHelper.GetRow(sheet, rowIndex + 3);
                npoiHelper.GetTableHeaderWarp(row, i).SetCellValue(b_dt.Columns[i].ColumnName);
            }
        }
        //Thêm tiêu đề
        row = NpoiHelper.GetRow(sheet, rowIndex);
        sheet.SetColumnWidth(b_dt.Columns.Count, 5000);
        npoiHelper.GetTableHeaderWarp(sheet, row, b_dt.Columns.Count, rowIndex, rowIndex + 3, b_dt.Columns.Count, b_dt.Columns.Count).SetCellValue("Điểm trung bình tốt nghiệp");
        sheet.SetColumnWidth(b_dt.Columns.Count + 1, 5000);
        npoiHelper.GetTableHeaderWarp(sheet, row, b_dt.Columns.Count + 1, rowIndex, rowIndex + 3, b_dt.Columns.Count + 1, b_dt.Columns.Count + 1).SetCellValue("Xếp hạng tốt nghiệp");

        rowIndex = rowIndex + 3;
        double diem, check;
        int hso, tong = 0;
        foreach (DataRow dataRow in b_dt.Rows)
        {
            row = NpoiHelper.GetRow(sheet, ++rowIndex);
            diem = 0; tong = 0; hso = 0;
            for (int i = 0; i < column_count; i++)
            {
                if (double.TryParse(dataRow[i].ToString(), out check) && i > 1)
                {
                    DataRow[] _row = b_dt_mon.Select("ma='" + b_dt.Columns[i].ColumnName + "'");
                    if (_row.Length > 0)
                    {
                        hso = LibConvert.ToInt(_row[0]["ten"]);
                        diem = diem + double.Parse(dataRow[i].ToString()) * hso;
                        tong = tong + hso;
                    }
                }
                if (b_dt.Columns[i].DataType.Name == "Decimal")
                {
                    if (dataRow[i].ToString() == "")
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(0.0);
                    }
                    else
                    {
                        npoiHelper.GetTableCellRight(row, i).SetCellValue(Double.Parse(dataRow[i].ToString()));
                    }
                }
                else
                    npoiHelper.GetTableCell(row, i).SetCellValue(dataRow[i].ToString());
            }
            diem = Math.Round(diem / tong, 1);
            npoiHelper.GetTableCellRight(row, column_count).SetCellValue(diem.ToString());
            string xep = "";// c_ht.Xep_Loai(diem);
            npoiHelper.GetTableCellRight(row, column_count + 1).SetCellValue(xep);
        }

        ExcelHelper.ToExcel(workbook, filename, response);
    }
    public static void ToExcel(DataTable dtInput, string filename, HttpResponse response)
    {
        DataSet dsInput = new DataSet();
        dsInput.Tables.Add(dtInput.Copy());
        ToExcel(dsInput, filename, response);
    }

    public static void ToExcel(HSSFWorkbook workbook, string fileName, HttpResponse response)
    {
        // Save the Excel spreadsheet to a file on the web server's file system
        using (var fileData = new FileStream(fileName, FileMode.Create))
        {
            workbook.Write(fileData);
        }

        using (var exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string saveAsFileName = string.Format(fileName + "-{0:d}.xls", DateTime.Now).Replace("/", "-");
            response.ContentType = "application/vnd.ms-excel";
            response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
            response.Clear();
            response.BinaryWrite(exportData.GetBuffer());
            //response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();
        }
    }
}

